﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class NoticeDAL
    {
        /// <summary>
        /// 获取通告类别列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetNoticeCategoryList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID,Name FROM E_Dict_NoticeCategory";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取通告列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public NoticeListModel GetNoticeListByCondition(SearchNoticeModel searchModel, int pageSize, int pageIndex)
        {
            NoticeListModel listModel = new NoticeListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", searchModel.CategoryID),
                new SqlParameter("@PublisherID", searchModel.PublisherID),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@FromDate", searchModel.FromDate),
                new SqlParameter("@ToDate", searchModel.ToDate),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@SysSelection", searchModel.SysSelection),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortNoticeModel> list = new List<ShortNoticeModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getNoticeListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortNoticeModel shortModel = new ShortNoticeModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.Title = dr["Title"].ToString();
                        shortModel.CategoryName = dr["CategoryName"].ToString();
                        shortModel.AddDate = (DateTime)dr["AddDate"];
                        shortModel.PublisherName = dr["PublisherName"].ToString();
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取公告信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public NoticeModel GetNoticeInfo(int id)
        {
            NoticeModel model = new NoticeModel();
            string sql = "SELECT ID,Title,CategoryID,CategoryName,PublisherID,PublisherName,AddDate,IsSys,Content FROM v_noticewithoutarea WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.PublisherID = (int)dr["PublisherID"];
                    model.PublisherName = dr["PublisherName"].ToString();
                    model.AddDate = (DateTime)dr["AddDate"];
                    model.IsSys = (bool)dr["IsSys"];
                    model.Content = dr["Content"].ToString();
                    model.AreaIDList = GetNoticeAreaIDList(model.ID);
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取公告地区ID列表
        /// </summary>
        /// <param name="noticeid"></param>
        /// <returns></returns>
        public IList<int> GetNoticeAreaIDList(int noticeid)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT AreaID FROM E_Notice2Area WHERE NoticeID = " + noticeid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0]);
                }
                return list;
            }
        }

        /// <summary>
        /// 添加公告
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddNotice(NoticeModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@AddDate", model.AddDate),
                new SqlParameter("@PublisherID", model.PublisherID),
                new SqlParameter("@IsSys", model.IsSys)
            };
            try
            {
                string sql = "INSERT INTO E_Notice(Title,CategoryID,Content,AddDate,PublisherID,IsSys)VALUES(@Title,@CategoryID,@Content,@AddDate,@PublisherID,@IsSys); SELECT @@IDENTITY;";
                int id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql, parms));
                ArrayList sqllist = new ArrayList();
                NoticeAreaHandle(id, model.AreaStrList, sqllist);
                int rst = SqlHelper.ExecuteSqlTran(sqllist);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception ex)
            {
                return -2;
            }
        }

        /// <summary>
        /// 更新公告
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateNotice(NoticeModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@IsSys", model.IsSys)
            };
            try
            {
                string sql = "UPDATE E_Notice SET Title = @Title, CategoryID = @CategoryID, Content = @Content, IsSys = @IsSys WHERE ID = " + model.ID;
                int rst = SqlHelper.ExecuteNonQueryBySql(sql, parms);
                ArrayList sqllist = new ArrayList();
                NoticeAreaHandle(model.ID, model.AreaStrList, sqllist);
                rst += SqlHelper.ExecuteSqlTran(sqllist);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception ex)
            {
                return -2;
            }
        }

        /// <summary>
        /// 删除公告
        /// </summary>
        /// <param name="noticeid"></param>
        /// <returns></returns>
        public int DelNotice(string[] noticeid)
        {
            string sql = "DELETE FROM E_Notice WHERE " + DALUtils.GetInCondition("ID", noticeid) + "; DELETE FROM E_Notice2Area WHERE " + DALUtils.GetInCondition("NoticeID", noticeid);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 公告地区处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public void NoticeAreaHandle(int id, string[] areaid, ArrayList sqllist)
        {
            sqllist.Add("DELETE FROM E_Notice2Area WHERE NoticeID = " + id);
            foreach (string s in areaid)
            {
                sqllist.Add("INSERT INTO E_Notice2Area(NoticeID,AreaID)VALUES(" + id + "," + s + ");");
            }
        }


    }
}
